﻿using com.uplus.common;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using System;
using System.Collections.Generic;
using System.Text;
using WebApi.Models;

namespace WebApi.Bill.Biz
{
    /// <summary>
    /// 网站展示表管理
    /// </summary>
    public  class FiltersSql
    {

        #region Get
        /// <summary>
        /// 获得查询条件
        /// </summary>
        /// <param name="filters"></param>
        /// <returns></returns>
        public static JObject GetQueryCond(dynamic filters)
        {
            JObject joData = new JObject();
            JObject jo = JObject.Parse(JsonConvert.SerializeObject(filters));
            string sqlAppend = "";
            int pageNum = 1;
            int pageSize = 20;
            string pkCode = null;
            string content = null;
            foreach (JProperty item in jo.Properties())
            {
                if (!string.IsNullOrEmpty(item.Value.ToString()))
                {
                    switch (item.Name)
                    {
                        case "sName":
                            sqlAppend += $" and sName like '%{item.Value}%'";
                            break;
                        case "sRoomName":
                            sqlAppend += $" and sRoomName = '{item.Value}'";
                            break;
                        case "fkMeeting":
                            sqlAppend += $" and fkMeeting = '{item.Value}'";
                            break;
                        case "tStartDate":
                            sqlAppend += $" and tStartDate >'{item.Value}'";
                            break;
                        case "tEndDate":
                            sqlAppend += $" and tEndDate < '{item.Value}'";
                            break;
                        case "PageNum":
                            pageNum = Convert.ToInt32(item.Value);
                            break;
                        case "PageSize":
                            pageSize = Convert.ToInt32(item.Value);
                            break;
                        case "sDescription":
                            sqlAppend += $" and sDescription like '%{item.Value}%'";
                            break;
                        case "des":
                            sqlAppend += $" and fkModule = '{item.Value}'";
                            break;
                        case "pkCode":
                            pkCode = item.Value.ToString();
                            break;
                        case "sContent":
                            sqlAppend += $" and sContent like '%{item.Value}%'";
                            break;
                        case "DevCode":
                            sqlAppend += $" and fkTer = '{item.Value}'";
                            break;
                        case "fkTaskList":
                            sqlAppend += $" and {item.Name} = '{item.Value}'";
                            break;
                        case "fkTaskListDuration":
                            sqlAppend += $" and {item.Name} = '{item.Value}'";
                            break;
                        case "terCode":
                            sqlAppend += $" and fkTer = '{item.Value}'";
                            break;
                        case "terminalCode":
                            sqlAppend += $" and pkCode like '%{item.Value}%'";
                            break;
                        case "date":
                            sqlAppend += $" and tCreate >= '{Convert.ToDateTime($"{item.Value} 00:00:00")}' and tCreate <= '{Convert.ToDateTime($"{item.Value} 23:59:59")}'";
                            break;
                        case "sTers":
                            sqlAppend += $" and sTers like '%{item.Value}%'";
                            break;
                        case "sTersName":
                            sqlAppend += $" and sTersName like '%{item.Value}%'";
                            break;
                        case "fkDepartment":
                            sqlAppend += $" and fkDepartment  = '{item.Value}'";
                            break;
                        case "fkTerminal":
                            sqlAppend += $" and fkTerminal  = '{item.Value}'";
                            break;
                        case "nType":
                            sqlAppend += $" and nType  = '{item.Value}'";
                            break;
                        case "nAudit":
                            sqlAppend += $" and nAudit  = '{item.Value}'";
                            break;
                        case "nLevel":
                            sqlAppend += $" and nLevel={item.Value}";
                            break;
                        case "NType":
                            sqlAppend += $" and nType={item.Value}";
                            break;
                        case "sEmail":
                            sqlAppend += $" and sEmail like '%{item.Value}%'";
                            break;
                        case "iDCard":
                            sqlAppend += $" and sIDCard like '%{item.Value}%'";
                            break;
                        case "nSignIn":
                            sqlAppend += $" and nSignIn={item.Value}";
                            break;
                        case "nSignOut":
                            sqlAppend += $" and nSignOut={item.Value}";
                            break;
                        case "sCompany":
                            sqlAppend += $" and fkCompany='{item.Value}'";
                            break;
                        case "sPhone":
                            sqlAppend += $" and sPhone like '%{item.Value}%'";
                            break;
                        case "nSource":
                            sqlAppend += $" and nSource = {item.Value}";
                            break;
                        case "sTheme":
                            sqlAppend += $" and fkTheme='{item.Value}'";
                            break;
                        case "nState":
                            sqlAppend += $" and nState={item.Value}";
                            break;
                        case "inviteTime":
                            List<string> str = JsonConvert.DeserializeObject<List<string>>(JsonConvert.SerializeObject(item.Value));
                            if(str.Count>0)
                            {
                                string start = str[0];
                                string end = str[1];
                                sqlAppend += $" and tStartDate>='{start} 00:00:00' and tEndDate<='{end} 23:59:00' ";
                            }
                            
                            break;
                        case "tSignInDate":
                            List<string> signIn = JsonConvert.DeserializeObject<List<string>>(JsonConvert.SerializeObject(item.Value));
                            if (signIn.Count > 0)
                            {
                                string start = signIn[0];
                                string end = signIn[1];
                                sqlAppend += $" and tSignInDate>='{start} 00:00:00' and tSignInDate<='{end} 23:59:00' ";
                            }
                            break;
                        case "tSignOutDate":
                            List<string> signOut = JsonConvert.DeserializeObject<List<string>>(JsonConvert.SerializeObject(item.Value));
                            if (signOut.Count > 0)
                            {
                                string start = signOut[0];
                                string end = signOut[1];
                                sqlAppend += $" and tSignInDate>='{start} 00:00:00' and tSignInDate<='{end} 23:59:00'";
                            }
                            
                            break;
                        case "tCreate":
                            List<string> creater = JsonConvert.DeserializeObject<List<string>>(JsonConvert.SerializeObject(item.Value));
                            if (creater.Count > 0)
                            {
                                string start = creater[0];
                                string end = creater[1];
                                sqlAppend += $" and tCreate>='{start} 00:00:00' and tCreate<='{end} 23:59:00'";
                            }

                            break;
                        case "STemplateName":
                            sqlAppend += $" and sTemplateName like '%{item.Value}%' ";
                            break;

                        case "TPay":
                            dynamic result = JsonConvert.DeserializeObject<dynamic>(JsonConvert.SerializeObject(item.Value));
                            if (result != null)
                            {
                                string[] dateArr = JsonConvert.DeserializeObject<string[]>(JsonConvert.SerializeObject(result));
                                sqlAppend += $" and DATE_FORMAT(tPay,'%Y-%m-%d %H:%i:%s') >='{Convert.ToDateTime(dateArr[0]).ToString("yyyy-MM-dd HH:mm:ss")}' and DATE_FORMAT(tPay,'%Y-%m-%d %H:%i:%s') <='{Convert.ToDateTime(dateArr[1]).ToString("yyyy-MM-dd HH:mm:ss")}' ";
                            }
                            break;
                        case "tWalletLogFinish":
                            dynamic resWall = JsonConvert.DeserializeObject<dynamic>(JsonConvert.SerializeObject(item.Value));
                            if (resWall != null)
                            {
                                string[] dateArr = JsonConvert.DeserializeObject<string[]>(JsonConvert.SerializeObject(resWall));
                                sqlAppend += $" and DATE_FORMAT(tFinish,'%Y-%m-%d') >='{Convert.ToDateTime(dateArr[0]).ToString("yyyy-MM-dd")}' and DATE_FORMAT(tFinish,'%Y-%m-%d') <='{Convert.ToDateTime(dateArr[1]).ToString("yyyy-MM-dd")}' ";
                            }
                            break;
                        case "TPutGarbage":
                            dynamic putResult = JsonConvert.DeserializeObject<dynamic>(JsonConvert.SerializeObject(item.Value));
                            if (putResult != null)
                            {
                                string[] dateArr = JsonConvert.DeserializeObject<string[]>(JsonConvert.SerializeObject(putResult));
                                sqlAppend += $" and DATE_FORMAT(tCreate,'%Y-%m-%d') >='{Convert.ToDateTime(dateArr[0]).ToString("yyyy-MM-dd")}' and DATE_FORMAT(tCreate,'%Y-%m-%d') <='{Convert.ToDateTime(dateArr[1]).ToString("yyyy-MM-dd")}' ";
                            }
                            break;
                        case "TViewTerWelfare":
                            dynamic terWelfare = JsonConvert.DeserializeObject<dynamic>(JsonConvert.SerializeObject(item.Value));
                            if (terWelfare != null)
                            {
                                string[] dateArr = JsonConvert.DeserializeObject<string[]>(JsonConvert.SerializeObject(terWelfare));
                                sqlAppend += $" and DATE_FORMAT(tCreate,'%Y-%m-%d') >='{Convert.ToDateTime(dateArr[0]).ToString("yyyy-MM-dd")}' and DATE_FORMAT(tCreate,'%Y-%m-%d') <='{Convert.ToDateTime(dateArr[1]).ToString("yyyy-MM-dd")}' ";
                            }
                            break;
                        case "SClerkName":
                            sqlAppend += $" and sClerkName like '%{item.Value}%'";
                            break;
                        case "sClerkName":
                            sqlAppend += $" and sClerkName like '%{item.Value}%'";
                            break;
                        case "SClerkCode":
                            sqlAppend += $" and SClerkCode like '%{item.Value}%'";
                            break;
                        case "TFinish":
                            dynamic resFinish = JsonConvert.DeserializeObject<dynamic>(JsonConvert.SerializeObject(item.Value));
                            if (resFinish != null)
                            {
                                string[] dateArr = JsonConvert.DeserializeObject<string[]>(JsonConvert.SerializeObject(resFinish));
                                sqlAppend += $" and DATE_FORMAT(tCreate,'%Y-%m-%d') >='{Convert.ToDateTime(dateArr[0]).ToString("yyyy-MM-dd")}' and DATE_FORMAT(tCreate,'%Y-%m-%d') <='{Convert.ToDateTime(dateArr[1]).ToString("yyyy-MM-dd")}' ";
                            }
                            break;
                        
                        case "nOperateType":
                            sqlAppend += $" and nOperateType = {item.Value} ";
                            break;
                        case "TCreate":
                            dynamic resUpCre = JsonConvert.DeserializeObject<dynamic>(JsonConvert.SerializeObject(item.Value));
                            if (resUpCre != null)
                            {
                                string[] dateArr = JsonConvert.DeserializeObject<string[]>(JsonConvert.SerializeObject(resUpCre));
                                sqlAppend += $" and DATE_FORMAT(tCreate,'%Y-%m-%d') >='{Convert.ToDateTime(dateArr[0]).ToString("yyyy-MM-dd")}' and DATE_FORMAT(tCreate,'%Y-%m-%d') <='{Convert.ToDateTime(dateArr[1]).ToString("yyyy-MM-dd")}' ";
                            }
                            //sqlAppend += $" and DATE_FORMAT(tCreate,'%Y-%m-%d %H:%i:%s') = '{Convert.ToDateTime(item.Value).ToString("yyyy-MM-dd HH:mm:ss")}'";
                            break;
                        case "TPersonOperate":
                            dynamic resPer = JsonConvert.DeserializeObject<dynamic>(JsonConvert.SerializeObject(item.Value));
                            if (resPer != null)
                            {
                                string[] dateArr = JsonConvert.DeserializeObject<string[]>(JsonConvert.SerializeObject(resPer));
                                sqlAppend += $" and DATE_FORMAT(tOperate,'%Y-%m-%d') >='{Convert.ToDateTime(dateArr[0]).ToString("yyyy-MM-dd")}' and DATE_FORMAT(tOperate,'%Y-%m-%d') <='{Convert.ToDateTime(dateArr[1]).ToString("yyyy-MM-dd")}' ";
                            }
                            break;
                        case "tPutCreate":
                            dynamic resPut = JsonConvert.DeserializeObject<dynamic>(JsonConvert.SerializeObject(item.Value));
                            if (resPut != null)
                            {
                                string[] dateArr = JsonConvert.DeserializeObject<string[]>(JsonConvert.SerializeObject(resPut));
                                sqlAppend += $" and DATE_FORMAT(tCreate,'%Y-%m-%d') >='{Convert.ToDateTime(dateArr[0]).ToString("yyyy-MM-dd")}' and DATE_FORMAT(tCreate,'%Y-%m-%d') <='{Convert.ToDateTime(dateArr[1]).ToString("yyyy-MM-dd")}' ";
                            }
                            break;
                        case "tRecycleCreate":
                            dynamic resRecycle = JsonConvert.DeserializeObject<dynamic>(JsonConvert.SerializeObject(item.Value));
                            if (resRecycle != null)
                            {
                                string[] dateArr = JsonConvert.DeserializeObject<string[]>(JsonConvert.SerializeObject(resRecycle));
                                sqlAppend += $" and DATE_FORMAT(tCreate,'%Y-%m-%d') >='{Convert.ToDateTime(dateArr[0]).ToString("yyyy-MM-dd")}' and DATE_FORMAT(tCreate,'%Y-%m-%d') <='{Convert.ToDateTime(dateArr[1]).ToString("yyyy-MM-dd")}' ";
                            }
                            //sqlAppend += $" and DATE_FORMAT(tCreate,'%Y-%m-%d') = '{Convert.ToDateTime(item.Value).ToString("yyyy-MM-dd")}'";
                            break;
                        case "orderNum":
                            switch (Convert.ToInt32(item.Value))
                            {
                                case 1:
                                    sqlAppend += $" GROUP BY fkVip ORDER BY CAST(SUM(sMoney) AS DECIMAL(15,2)) DESC";
                                    break;
                                case 2:
                                    sqlAppend += $" GROUP BY fkVip ORDER BY CAST(SUM(sMoney) AS DECIMAL(15,2)) ";
                                    break;
                                default:
                                    sqlAppend += $" GROUP BY fkVip ORDER BY CAST(SUM(sMoney) AS DECIMAL(15,2)) DESC";
                                    break;
                            }
                            break;
                        case "terWelOrderNum":
                            switch (Convert.ToInt32(item.Value))
                            {
                                case 1:
                                    sqlAppend += $"  order by tCreate desc";
                                    break;
                                case 2:
                                    sqlAppend += $" order by tCreate ";
                                    break;
                                default:
                                    sqlAppend += $"  order by tCreate desc";
                                    break;
                            }
                            break;
                        case "terPutGarbageOrderNum":
                            switch (Convert.ToInt32(item.Value))
                            {
                                case 1:
                                    sqlAppend += $"  order by tCreate desc";
                                    break;
                                case 2:
                                    sqlAppend += $" order by tCreate ";
                                    break;
                                default:
                                    sqlAppend += $"  order by tCreate desc";
                                    break;
                            }
                            break;
                        case "tOrderPay":
                            switch (Convert.ToInt32(item.Value))
                            {
                                case 1:
                                    sqlAppend += $"  order by tPay desc";
                                    break;
                                case 2:
                                    sqlAppend += $" order by tPay ";
                                    break;
                                default:
                                    sqlAppend += $"  order by tPay desc";
                                    break;
                            }
                            break;
                        case "tPayRecord":
                            switch (Convert.ToInt32(item.Value))
                            {
                                case 1:
                                    sqlAppend += $"  order by tPay desc";
                                    break;
                                case 2:
                                    sqlAppend += $" order by tPay ";
                                    break;
                                default:
                                    sqlAppend += $"  order by tPay desc";
                                    break;
                            }
                            break;
                        case "NStock":
                            switch (Convert.ToInt32(item.Value))
                            {
                                case 1:
                                    sqlAppend += $"  order by nStock desc";
                                    break;
                                case 2:
                                    sqlAppend += $" order by nStock ";
                                    break;
                                default:
                                    sqlAppend += $"  order by nStock desc";
                                    break;
                            }
                            break;
                        case "TCreateRecycleOrder":
                            switch (Convert.ToInt32(item.Value))
                            {
                                case 1:
                                    sqlAppend += $"  order by tCreate desc";
                                    break;
                                case 2:
                                    sqlAppend += $" order by tCreate ";
                                    break;
                                default:
                                    sqlAppend += $"  order by tCreate desc";
                                    break;
                            }
                            break;
                        case "tInspectRecord":
                            switch (Convert.ToInt32(item.Value))
                            {
                                case 1:
                                    sqlAppend += $"  order by tCreate desc";
                                    break;
                                case 2:
                                    sqlAppend += $" order by tCreate ";
                                    break;
                                default:
                                    sqlAppend += $"  order by tCreate desc";
                                    break;
                            }
                            break;
                        case "tTerProductLog":
                            switch (Convert.ToInt32(item.Value))
                            {
                                case 1:
                                    sqlAppend += $"  order by tDate desc";
                                    break;
                                case 2:
                                    sqlAppend += $" order by tDate ";
                                    break;
                                default:
                                    sqlAppend += $"  order by tDate desc";
                                    break;
                            }
                            break;

                        case "terDataOrderNum":
                            switch (Convert.ToInt32(item.Value))
                            {
                                case 1:
                                    sqlAppend += $"  order by STerNameNoNum DESC ,orderNum   DESC";
                                    break;
                                case 2:
                                    sqlAppend += $" order by  STerNameNoNum  ,orderNum  ";
                                    break;
                                default:
                                    sqlAppend += $"  order by STerNameNoNum DESC ,orderNum   DESC";
                                    break;
                            }
                            break;
                        case "terPaperOrderNum":
                            switch (Convert.ToInt32(item.Value))
                            {
                                case 1:
                                    sqlAppend += $"  order by nStock desc";
                                    break;
                                case 2:
                                    sqlAppend += $" order by nStock ";
                                    break;
                                default:
                                    sqlAppend += $"  order by nStock desc";
                                    break;
                            }
                            break;
                        case "recOrderNum":
                            switch (Convert.ToInt32(item.Value))
                            {
                                case 1:
                                    sqlAppend += $"  order by nWeight DESC ";
                                    break;
                                case 2:
                                    sqlAppend += $" order by nWeight ";
                                    break;
                                default:
                                    sqlAppend += $"  order by nWeight DESC";
                                    break;
                            }
                            break;

                        case "terVipOrderNum":
                            switch (Convert.ToInt32(item.Value))
                            {
                                case 1:
                                    sqlAppend += $" GROUP BY fkVip ORDER BY CAST(SUM(sWeight) AS DECIMAL(15,2)) DESC) AS a LEFT JOIN (SELECT fkVip,COUNT(*) AS BuyCount,COUNT(*) AS BuyNum FROM TbVipWalletLog GROUP BY fkVip) AS b ON b.fkVip=a.fkVip ";
                                    break;
                                case 2:
                                    sqlAppend += $" GROUP BY fkVip ORDER BY CAST(SUM(sWeight) AS DECIMAL(15,2))) AS a LEFT JOIN (SELECT fkVip,COUNT(*) AS BuyCount,COUNT(*) AS BuyNum FROM TbVipWalletLog GROUP BY fkVip) AS b ON b.fkVip=a.fkVip ";
                                    break;
                                default:
                                    sqlAppend += $" GROUP BY fkVip ORDER BY CAST(SUM(sWeight) AS DECIMAL(15,2)) DESC) AS a LEFT JOIN (SELECT fkVip,COUNT(*) AS BuyCount,COUNT(*) AS BuyNum FROM TbVipWalletLog GROUP BY fkVip) AS b ON b.fkVip=a.fkVip ";
                                    break;
                            }
                            break;
                        case "Code":
                            sqlAppend += $" and pkCode like '%{item.Value}%'";
                            break;
                        case "FkVip":
                            sqlAppend += $" and fkVip like '%{item.Value}%'";
                            break;
                        case "fkVip":
                            sqlAppend += $" and fkVip like '%{item.Value}%'";
                            break;
                        case "sTerCode":
                            sqlAppend += $" and sTerCode like '%{item.Value}%'";
                            break;
                        default:
                            //sqlAppend += $" and {item.Name} = '{item.Value}'";
                            break;
                    }
                }
            }
            joData.Add("sqlAppend", sqlAppend);
            joData.Add("pageNum", pageNum);
            joData.Add("pageSize", pageSize);
            joData.Add("pkCode", pkCode);
            joData.Add("content", content);
            return joData;
        }
        #endregion

        public static bool CheckData(string inputData)
        {
            bool re = false;
            string StrRegex = "EXEC|UNION|SELECT|UPDATE|SET|INSERT|INTO|FROM|";
            string StrKeyWord = "select|insert|delete|from|count(|drop table|update|truncate|asc(|mid(|char(|xp_cmdshell|exec|master|net local group administrators|net user|or|and";
            string StrSymbol = ";^(^)^[^]^{^}^%^@^*^'^!^||^|";

            string[] StrRegexWord = StrRegex.Split('|');
            foreach (string sqlParam in StrRegexWord)
            {
                if (inputData.Contains(sqlParam + " ") || inputData.Contains(" " + sqlParam))
                {
                    re = true;
                }
            }

            string[] pattenKeyWord = StrKeyWord.Split('|');
            string[] pattenSymbol = StrSymbol.Split('^');
            foreach (string sqlParam in pattenKeyWord)
            {
                if (inputData.Contains(sqlParam + " ") || inputData.Contains(" " + sqlParam))
                {
                    re = true;
                }
            }
            foreach (string sqlParam in pattenSymbol)
            {
                if (inputData.Contains(sqlParam))
                {
                    re = true;
                }
            }
            return re;
        }
    }
}
